package com.tuyue.police.dao;

import com.tuyue.police.pojo.PoliceInfo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @Author: 王金海
 * @Description:
 * @Date: Created by Administrator on 2018/3/13.
 * @Modified By:
 */
public interface IPoliceInfoDao extends JpaRepository<PoliceInfo,Long> {
    @Query("select p from PoliceInfo p where p.userId=?1")
    Page<PoliceInfo> findByUserId(Long userId, Pageable pageable);

//    @Query("select function('date_format',p.pInfoTime,'%H') as day from PoliceInfo p group by function('date_format',p.pInfoTime,'%H') order by p.pInfoTime asc ")
    @Query(value = "SELECT HOUR(p.p_info_time) as Hour,count(*) as Count FROM police_info p WHERE date_format(p.p_info_time,'%Y-%m-%d')=date_format(NOW(),'%Y-%m-%d') AND p.user_id=?1 GROUP BY HOUR(p.p_info_time) ORDER BY Hour(p.p_info_time)",nativeQuery = true)
    List findGroupTime(Long userId);
}
